package dao.impl;

import dao.NoteDao;
import pojo.Note;
import util.DataUtils;
import util.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class NoteDaoImpl implements NoteDao {
    private boolean flag = false;
    private Connection conn = null;
    private ResultSet rs = null;


    /**
     * 添加笔记
     *
     * @param note
     * @return
     */
    @Override
    public boolean addNote(Note note) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer i = 0;
        String sql = "insert into biji values(0,'" + note.getContent() + "','" + note.getTitle() + "','" + note.getType() + "','" + note.getUsename() + "','" + 0 + "','" + note.getFavourite() + "','" + note.getLikes() + "')";
        i = JdbcUtils.addsql(sql);
        if (i > 0) flag = true;
        JdbcUtils.closeconn(null, null, conn);
        return flag;
    }

    /**
     * 修改笔记内容
     *
     * @param nid  获取客户端请求修改的笔记的id，根据id修改笔记内容
     * @param note 封装好了的笔记对象
     * @return
     */
    public boolean updateNote(Integer nid, Note note) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer i = 0;
        String sql = "update biji set title='" + note.getTitle() + "',content='" + note.getContent() + "',type='" + note.getType() + "',style='" + 0 + "',favourite='" + 0 + "',likes='" + 0 + "' where nid='" + nid + "'";
        i = JdbcUtils.addsql(sql);
        if (i > 0) flag = true;
        JdbcUtils.closeconn(null, null, conn);
        return flag;
    }

    /**
     * 根据nid删除note
     *
     * @param nid
     * @return
     */
    public boolean delNote(Integer nid) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer i = 0;
        String sql = "delete from biji where nid='" + nid + "'";
        i = JdbcUtils.addsql(sql);
        if (i > 0) flag = true;
        JdbcUtils.closeconn(null, null, conn);
        return flag;
    }


    /**
     * 查看所有用户发布的所有笔记---for系统管理员
     *
     * @return
     */
    @Override
    public ArrayList<Note> allNotes() throws SQLException, ClassNotFoundException {
        ArrayList<Note> listnotesOfUsers = new ArrayList<>();
        conn = JdbcUtils.dbconn();
        rs = JdbcUtils.selectsql("select * from biji");
//        while (true) {
//            if (!rs.next()) break;
//            if (rs.getInt("style") == 1) {
//                Note note = new Note();
//                note.setStyle(rs.getInt("style"));
//                note.setNid(rs.getInt("nid"));
//                note.setTitle(rs.getString("title"));
//                note.setContent(rs.getString("content"));
//                note.setType(rs.getString("type"));
//                note.setUsename(rs.getString("usename"));
//                note.setLikes(rs.getInt("likes"));
//                note.setFavourite(rs.getInt("favourite"));
//                listnotesOfUsers.add(note);
//            }
//        }
        rs.last();
        int row = rs.getRow();
        rs.beforeFirst();
        do {
            row--;
            Note note = new Note();
            note = (Note) DataUtils.dataEncapsulation(Class.forName("pojo.Note"), rs);
            listnotesOfUsers.add(note);
        } while (row > 0);
        JdbcUtils.closeconn(rs, null, conn);
        return listnotesOfUsers;
    }

    /**
     * 查看某个具体用户的所有笔记
     *
     * @param usename
     * @return
     */
    public ArrayList<Note> userAllNotes(String usename) throws SQLException, ClassNotFoundException {
        ArrayList<Note> useallnote = new ArrayList<>();
        conn = JdbcUtils.dbconn();
        rs = JdbcUtils.selectsql("select * from biji where usename='" + usename + "'");
//        while (true) {
//            if (!rs.next()) break;
//            if (rs.getInt("style") == 1) {
//                Note note = new Note();
//                note.setStyle(rs.getInt("style"));
//                note.setNid(rs.getInt("nid"));
//                note.setTitle(rs.getString("title"));
//                note.setContent(rs.getString("content"));
//                note.setType(rs.getString("type"));
//                note.setUsename(rs.getString("usename"));
//                note.setLikes(rs.getInt("likes"));
//                note.setFavourite(rs.getInt("favourite"));
//                useallnote.add(note);
//            }
//        }
        rs.last();
        int row = rs.getRow();
        rs.beforeFirst();
        do {
            row--;
            Note note = new Note();
            note = (Note) DataUtils.dataEncapsulation(Class.forName("pojo.Note"), rs);
            useallnote.add(note);
        } while (row > 0);
        JdbcUtils.closeconn(rs, null, conn);
        return useallnote;
    }

    /**
     * 根据id查找某个笔记--用于用户修改笔记
     *
     * @param nid
     * @return
     */
    public Note findNote(Integer nid) throws SQLException, ClassNotFoundException {
        Note note = new Note();
        conn = JdbcUtils.dbconn();
        rs = JdbcUtils.selectsql("select * from biji where nid='" + nid + "' and style='" + 1 + "'");
//        while (true) {
//            if (!rs.next()) break;
//            note.setStyle(rs.getInt("style"));
//            note.setNid(rs.getInt("nid"));
//            note.setTitle(rs.getString("title"));
//            note.setContent(rs.getString("content"));
//            note.setType(rs.getString("type"));
//            note.setUsename(rs.getString("usename"));
//            note.setLikes(rs.getInt("likes"));
//            note.setFavourite(rs.getInt("favourite"));
//        }
        note = (Note) DataUtils.dataEncapsulation(Class.forName("pojo.Note"), rs);
        JdbcUtils.closeconn(rs, null, conn);
        return note;
    }

    /**
     * 查看用户审核中的笔记
     *
     * @param usename
     * @return
     */
    public ArrayList<Note> userCheckedNote(String usename) throws SQLException, ClassNotFoundException {
        ArrayList<Note> listCeckedNote = new ArrayList<>();
        conn = JdbcUtils.dbconn();
        rs = JdbcUtils.selectsql("select * from biji where usename='" + usename + "' and style='" + 0 + "'");
//        while (true) {
//            if (!rs.next()) break;
//            Note note = new Note();
//            note.setStyle(rs.getInt("style"));
//            note.setNid(rs.getInt("nid"));
//            note.setTitle(rs.getString("title"));
//            note.setContent(rs.getString("content"));
//            note.setType(rs.getString("type"));
//            note.setUsename(rs.getString("usename"));
//            note.setLikes(rs.getInt("likes"));
//            note.setFavourite(rs.getInt("favourite"));
//            listCeckedNote.add(note);
//        }
        rs.last();
        int row = rs.getRow();
        rs.beforeFirst();
        do {
            row--;
            Note note = new Note();
            note = (Note) DataUtils.dataEncapsulation(Class.forName("pojo.Note"), rs);
            listCeckedNote.add(note);
        } while (row > 0);
        JdbcUtils.closeconn(rs, null, conn);
        return listCeckedNote;
    }

    /**
     * 修改笔记状态
     *
     * @param nid
     * @return
     */
    public boolean updateStyle(Integer nid) throws SQLException {
        Integer newstyle = 1;
        conn = JdbcUtils.dbconn();
        String sql = "update biji set style='" + newstyle + "' where nid='" + nid + "'";
        Integer i = JdbcUtils.addsql(sql);
        if (i > 0) flag = true;
        JdbcUtils.closeconn(null, null, conn);
        return flag;
    }

    /**
     * 查询总笔记数
     *
     * @return
     */
    public Integer totalNotes() throws SQLException {
        JdbcUtils.dbconn();
        Integer number = 0;
        String sql = "select * from biji";
        rs = JdbcUtils.selectsql(sql);
        while (true) {
            if (!rs.next()) break;
            if (rs.getInt("style") == 1)
                number++;
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 查询某区域总笔记数
     *
     * @return
     */
    public Integer AreaTotalNotes(String type) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        String sql = "select * from biji where type='" + type + "'";
        rs = JdbcUtils.selectsql(sql);
        while (true) {
            if (!rs.next()) break;
            if (rs.getInt("style") == 1)
                number++;
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 获取用户发布通过的笔记数量
     *
     * @param usename
     * @return
     */
    public Integer userTotalNote(String usename) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        rs = JdbcUtils.selectsql("select * from biji where usename='" + usename + "'");
        while (true) {
            if (!rs.next()) break;
            if (rs.getInt("style") == 1) {
                number++;
            }
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 用户待审核笔记数量
     *
     * @param usename
     * @return
     */
    public Integer userTotalCheckedNote(String usename) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        rs = JdbcUtils.selectsql("select * from biji where usename='" + usename + "' and style='" + 0 + "'");
        while (true) {
            if (!rs.next()) break;
            number++;
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 具体区域审核笔记数
     *
     * @param type
     * @return
     */
    public Integer areaCheckedNotes(String type) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        rs = JdbcUtils.selectsql("select * from biji where type='" + type + "' and style='" + 0 + "'");
        while (true) {
            if (!rs.next()) break;
            number++;
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 查询某用户某区域总笔记数
     *
     * @return
     */
    public Integer userAreaTotalNotes(String usename, String type) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        String sql = "select * from biji where type='" + type + "' and usename='" + usename + "'";
        rs = JdbcUtils.selectsql(sql);
        while (true) {
            if (!rs.next()) break;
            if (rs.getInt("style") == 1)
                number++;
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }

    /**
     * 查询添加笔记的id
     *
     * @param note
     * @return
     */
    public Integer findUserNoteNid(Note note) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        String sql = "select nid from biji where usename='" + note.getUsename() + "' and title='" + note.getTitle() + "' and type='" + note.getType() + "' and content='" + note.getContent() + "'";
        rs = JdbcUtils.selectsql(sql);
        while (true) {
            if (!rs.next()) break;
            number = rs.getInt("nid");
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }


    /**
     * 查询搜索后总笔记数
     *
     * @return
     */
    public Integer afterSearchTotalNotes(String content) throws SQLException {
        conn = JdbcUtils.dbconn();
        Integer number = 0;
        String sql = "select * from label where label like '%+" + content + "%'";
        rs = JdbcUtils.selectsql(sql);
        while (true) {
            if (!rs.next()) break;
            number = rs.getInt("nid");
        }
        JdbcUtils.closeconn(rs, null, conn);
        return number;
    }
}